#!/usr/bin/env python
# -*- coding: utf-8 -*-

import SimpleHTTPServer
import SocketServer
import sqlite3
import csv
import sys

# Server

##PORT = 8002
##
##Handler = SimpleHTTPServer.SimpleHTTPRequestHandler
##
##httpd = SocketServer.TCPServer(("", PORT), Handler)
##
##print "serving at port", PORT

# Database

conexao = sqlite3.connect("rebides.db")

c = conexao.cursor()

c.execute('''create table if not exists rebides (id integer, nome_docente text, ultimo_grau text, curso text, categoria text, regime_tempo text, ano_lectivo integer, tipo_estabelecimento text, nome_estabelecimento text)''')


for x in range(0,10):
    x = csv.reader(open('rebcsv/reb0.csv', 'rb'), delimiter=',')
    stmt = '''insert into rebides (id, nome_docente, ultimo_grau, curso, categoria, regime_tempo, ano_lectivo, tipo_estabelecimento, nome_estabelecimento) values ("{0}", "{1}", "{2}", "{3}", "{4}", "{5}", "{6}", "{7}", "{8}")'''.\
           format("","","","","","","","","")
    c.execute(stmt)
    pass
conexao.commit()


c.execute('''select * from rebides''')

for x in c.fetchall():
    print x
    pass
c.execute('''select count(*) from rebides''')

for x in c.fetchall():
    print x
    pass

conexao.close()

# CSV

conexao = sqlite3.connect("rebides.db")
c = conexao.cursor()

ficheiro = file("index.html", "w")
ficheiro.write("<html> <body>")
spamReader = csv.reader(open('rebcsv/reb0.csv', 'rb'), delimiter=',')

ficheiro.write("<ul>")
for row in spamReader:
    stmt = '''insert into rebides (id, nome_docente, ultimo_grau, curso, categoria, regime_tempo, ano_lectivo, tipo_estabelecimento, nome_estabelecimento) values("{0}", "{1}", "{2}", "{3}", "{4}", "{5}", "{6}", "{7}", "{8}")'''.\
           format(str(row[0]), str(row[1]), str(row[2]),str(row[3]), str(row[4]), str(row[5]), str(row[6]), str(row[7]), str(row[8]))
    c.execute(stmt)
    ficheiro.write("<li>" + str(row[0]) + ' ' + str(row[1]) + ' ' + str(row[2]) + str(row[3]) + ' ' + str(row[4]) + ' ' + str(row[5]) + ' ' + str(row[6]) + ' ' + str(row[7]) + ' ' + str(row[8]))
    pass

ficheiro.write("</ul>")

ficheiro.write("</body> </html>")

ficheiro.close()
conexao.commit()
conexao.close()

#httpd.serve_forever()
